// process_bank_data_complete.js
// 完整处理银行流水数据

const SequelizeLib = require('sequelize');
const redis = require('redis');

// 数据库配置（使用项目中的配置）
const sequelize = new SequelizeLib({
  dialect: 'mysql',
  host: '127.0.0.1',
  port: 3306,
  username: 'root',
  password: '888mei525',
  database: 'lingxi_system',
  logging: false
});

// Redis配置
const redisClient = redis.createClient({
  host: '127.0.0.1',
  port: 6379
});

// 银行流水批次模型
const PluginDataBatchModel = sequelize.define('pluginDataBatch', {
  id: {
    type: SequelizeLib.DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  batchId: {
    type: SequelizeLib.DataTypes.STRING(100),
    unique: true
  },
  dataType: {
    type: SequelizeLib.DataTypes.STRING(50)
  },
  count: {
    type: SequelizeLib.DataTypes.INTEGER
  },
  status: {
    type: SequelizeLib.DataTypes.STRING(50)
  },
  processedCount: {
    type: SequelizeLib.DataTypes.INTEGER
  },
  failedCount: {
    type: SequelizeLib.DataTypes.INTEGER
  },
  processingTime: {
    type: SequelizeLib.DataTypes.INTEGER
  },
  createdAt: {
    type: SequelizeLib.DataTypes.DATE,
    field: 'created_at'
  },
  updatedAt: {
    type: SequelizeLib.DataTypes.DATE,
    field: 'updated_at'
  },
  completedAt: {
    type: SequelizeLib.DataTypes.DATE,
    field: 'completed_at'
  },
  errorMessage: {
    type: SequelizeLib.DataTypes.TEXT,
    field: 'error_message'
  }
}, {
  tableName: 'plugin_data_batches',
  timestamps: true,
  underscored: true
});

// 银行流水模型
const BankTransactionModel = sequelize.define('bankTransaction', {
  id: {
    type: SequelizeLib.DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  organizationId: {
    type: SequelizeLib.DataTypes.INTEGER,
    allowNull: false,
    field: 'organization_id'
  },
  transactionId: {
    type: SequelizeLib.DataTypes.STRING(200),
    allowNull: false,
    unique: true,
    field: 'transaction_id'
  },
  payerName: {
    type: SequelizeLib.DataTypes.STRING(200),
    allowNull: false,
    field: 'payer_name'
  },
  payerAccount: {
    type: SequelizeLib.DataTypes.STRING(100),
    field: 'payer_account'
  },
  amount: {
    type: SequelizeLib.DataTypes.DECIMAL(12, 2),
    allowNull: false
  },
  transactionDate: {
    type: SequelizeLib.DataTypes.DATE,
    allowNull: false,
    field: 'transaction_date'
  },
  transactionTime: {
    type: SequelizeLib.DataTypes.DATE,
    allowNull: false,
    field: 'transaction_time'
  },
  balance: {
    type: SequelizeLib.DataTypes.DECIMAL(12, 2)
  },
  remark: {
    type: SequelizeLib.DataTypes.TEXT
  },
  dataSource: {
    type: SequelizeLib.DataTypes.ENUM('file_import', 'plugin_capture'),
    defaultValue: 'file_import',
    field: 'data_source'
  },
  importBatch: {
    type: SequelizeLib.DataTypes.STRING(100),
    field: 'import_batch'
  },
  isFiltered: {
    type: SequelizeLib.DataTypes.BOOLEAN,
    defaultValue: false,
    field: 'is_filtered'
  },
  filterReason: {
    type: SequelizeLib.DataTypes.STRING(100),
    field: 'filter_reason'
  },
  createdAt: {
    type: SequelizeLib.DataTypes.DATE,
    field: 'created_at'
  },
  bankName: {
    type: SequelizeLib.DataTypes.STRING(200),
    field: 'bank_name'
  },
  currency: {
    type: SequelizeLib.DataTypes.STRING(50)
  },
  channel: {
    type: SequelizeLib.DataTypes.STRING(50)
  },
  fee: {
    type: SequelizeLib.DataTypes.DECIMAL(12, 2),
    defaultValue: 0
  },
  accountName: {
    type: SequelizeLib.DataTypes.STRING(200),
    field: 'account_name'
  },
  accountNumber: {
    type: SequelizeLib.DataTypes.STRING(100),
    field: 'account_number'
  }
}, {
  tableName: 'bank_transactions',
  timestamps: false
});

// 从插件数据中提取银行流水
function extractBankTransactionsFromPluginData(item) {
  try {
    // 检查数据结构
    if (!item || !item.response) {
      console.warn('[PluginData] 插件数据格式不正确，缺少response');
      return [];
    }
    
    const responseData = item.response.data || item.response;
    
    // 检查是否为杭州银行流水数据格式
    if (!responseData.data || !Array.isArray(responseData.data)) {
      console.warn('[PluginData] 银行流水数据格式不正确，缺少data数组');
      return [];
    }
    
    // 提取银行流水数据
    const transactions = responseData.data.map(bankItem => {
      // 判断是收入还是支出
      const debitAmount = parseFloat(bankItem.debitAmount) || 0;  // 借方金额（支出）
      const loanAmount = parseFloat(bankItem.loanAmount) || 0;    // 贷方金额（收入）
      
      // 如果借方金额大于0，表示支出；如果贷方金额大于0，表示收入
      const amount = debitAmount > 0 ? -debitAmount : loanAmount;
      
      return {
        // 基本字段
        transactionId: bankItem.orderFlowNo,                    // 流水号
        payerName: bankItem.recAccountName,                     // 对方户名
        payerAccount: bankItem.recAccount,                      // 对方账号
        amount: amount,                                         // 交易金额
        transactionDate: bankItem.transferDate,                 // 交易日期
        transactionTime: `${bankItem.transferDate} ${bankItem.transferTime}`, // 交易时间
        balance: parseFloat(bankItem.balance) || 0,             // 余额
        remark: `${bankItem.summary}${bankItem.memo ? ` - ${bankItem.memo}` : ''}`, // 备注
        
        // 新增字段
        bankName: bankItem.organName,                           // 银行名称
        currency: bankItem.currencyTypeHZ,                      // 币种
        channel: bankItem.channelCode,                          // 渠道
        fee: parseFloat(bankItem.tranFee) || 0,                 // 手续费
        accountName: responseData.accountName,                  // 己方账户名称
        accountNumber: responseData.accountNo                   // 己方账户号码
      };
    });
    
    console.log(`[PluginData] 成功提取 ${transactions.length} 条银行流水数据`);
    return transactions;
  } catch (error) {
    console.error('[PluginData] 提取银行流水数据失败:', error);
    return [];
  }
}

// 处理银行流水数据
async function processBankData(item, batchId) {
  try {
    console.log('[PluginData] 开始处理银行流水数据:', item.url);
    
    // 解析银行流水数据
    const bankTransactions = extractBankTransactionsFromPluginData(item);
    
    if (!bankTransactions || bankTransactions.length === 0) {
      console.warn('[PluginData] 未提取到银行流水数据');
      return { success: false, error: '未提取到银行流水数据' };
    }
    
    // 批量保存银行流水数据
    let savedCount = 0;
    
    for (const transaction of bankTransactions) {
      try {
        // 检查是否已存在相同的流水记录
        const existing = await BankTransactionModel.findOne({
          where: {
            transactionId: transaction.transactionId,
            amount: transaction.amount,
            transactionDate: transaction.transactionDate
          }
        });
        
        if (existing) {
          console.log(`[PluginData] 银行流水已存在，跳过: ${transaction.transactionId}`);
          continue;
        }
        
        // 保存新的银行流水记录
        const savedItem = await BankTransactionModel.create({
          ...transaction,
          importBatch: batchId,
          dataSource: 'plugin_capture',
          organizationId: 1 // 默认组织ID
        });
        
        savedCount++;
      } catch (saveError) {
        console.error(`[PluginData] 保存银行流水失败: ${transaction.transactionId}`, saveError);
      }
    }
    
    console.log(`[PluginData] 银行流水数据处理完成 - 成功保存: ${savedCount} 条`);
    
    return {
      success: true,
      transactionCount: savedCount
    };
  } catch (error) {
    console.error('[PluginData] 处理银行流水数据失败:', error);
    return { success: false, error: error.message };
  }
}

// 完整处理银行流水数据
async function processBankDataComplete() {
  try {
    console.log('🔍 连接数据库和Redis...');
    await sequelize.authenticate();
    await redisClient.connect();
    console.log('✅ 连接成功!');
    
    // 查找所有状态为'processing'且数据类型为'bank'的批次
    const processingBatches = await PluginDataBatchModel.findAll({
      where: {
        status: 'processing',
        dataType: 'bank'
      }
    });
    
    console.log(`📊 找到 ${processingBatches.length} 个待处理的银行流水批次`);
    
    // 处理每个批次
    for (const batch of processingBatches) {
      console.log(`🔄 处理批次: ${batch.batchId}`);
      
      // 从Redis中获取数据
      const key = `plugin_data_batch:${batch.batchId}`;
      const dataStr = await redisClient.get(key);
      
      if (!dataStr) {
        console.log(`⚠️  批次数据不存在或已过期: ${batch.batchId}`);
        // 更新批次状态为完成（因为数据已过期）
        await PluginDataBatchModel.update({
          status: 'completed',
          completedAt: new Date()
        }, {
          where: { batchId: batch.batchId }
        });
        continue;
      }
      
      const data = JSON.parse(dataStr);
      console.log(`📋 批次数据条数: ${data.length}`);
      
      // 处理银行流水数据
      let successCount = 0;
      let failedCount = 0;
      
      for (const item of data) {
        try {
          const result = await processBankData(item, batch.batchId);
          if (result.success) {
            successCount++;
          } else {
            failedCount++;
          }
        } catch (error) {
          console.error(`❌ 处理数据项失败:`, error);
          failedCount++;
        }
      }
      
      console.log(`✅ 批次处理完成: ${batch.batchId} - 成功: ${successCount}, 失败: ${failedCount}`);
      
      // 更新批次状态为完成
      await PluginDataBatchModel.update({
        status: 'completed',
        processedCount: successCount,
        failedCount: failedCount,
        completedAt: new Date()
      }, {
        where: { batchId: batch.batchId }
      });
    }
    
    console.log('✅ 所有银行流水批次处理完成!');
  } catch (error) {
    console.error('❌ 处理银行流水数据失败:', error.message);
    throw error;
  } finally {
    await sequelize.close();
    await redisClient.quit();
  }
}

// 主函数
async function main() {
  try {
    console.log('🚀 开始完整处理银行流水数据...');
    await processBankDataComplete();
    console.log('✅ 完整处理完成!');
  } catch (error) {
    console.error('❌ 处理失败:', error.message);
    process.exit(1);
  }
}

// 执行主函数
if (require.main === module) {
  main();
}

module.exports = {
  processBankDataComplete
};